package com.ruoyi.lc.util;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.ruoyi.common.utils.MapUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.lc.domain.LcDatamodelColumn;
import com.ruoyi.lc.domain.LcDatamodelDefine;
import com.ruoyi.lc.enums.LcDatamodelColumnAttrEnum;
import com.ruoyi.lc.enums.LcDatamodelColumnMappingTypeEnum;
import com.ruoyi.lc.enums.LcDatamodelColumnTypeEnum;

public class LcDataModelUtil {

	public static boolean isSystemColumn(LcDatamodelColumn column) {
		String codeUpper = column.getCode().toUpperCase();
		if(codeUpper.equals("ID") || codeUpper.equals("UPDATE_BY") || codeUpper.equals("CREATE_BY") || codeUpper.equals("CREATE_TIME") || codeUpper.equals("UPDATE_TIME") ||  codeUpper.equals("REMARK")) {
			return true;
		}
		return false;
	}
	
	
	public static List<LcDatamodelColumn> findColumnsFromDesc(String desc) {
		JSONObject j1 = JSON.parseObject(desc);
		Map<String, LcDatamodelColumn> oldc = parseFromJson(j1.getJSONArray("columns"));
		List<LcDatamodelColumn> result = new ArrayList<LcDatamodelColumn>();
		result.addAll(oldc.values());
		result = result.stream()
                .sorted((p1, p2) -> p1.getOrderNum() - p2.getOrderNum())
                .collect(Collectors.toList());
		return result;
	}
	
	public static String generateAlterTableSql(LcDatamodelDefine define) {
		String desc1 = define.getDesc();
		String desc2 = define.getWhzDesc();
		JSONObject j1 = JSON.parseObject(desc1);
		JSONObject j2 = JSON.parseObject(desc2);
		String defineCode = j1.getString("code");
		String oldDefineTitle = j1.getString("title");
		String newDefineTitle = j2.getString("title");
		Map<String, LcDatamodelColumn> oldc = parseFromJson(j1.getJSONArray("columns"));
		Map<String, LcDatamodelColumn> newc = parseFromJson(j2.getJSONArray("columns"));
		return generateAlterTableSql(defineCode, oldDefineTitle, newDefineTitle, oldc, newc);
	}
	
	private static Map<String, LcDatamodelColumn> parseFromJson(JSONArray columnsArray) {
		Map<String, LcDatamodelColumn> result = new HashMap<>();
		for(int i = 0; i < columnsArray.size(); i++) {
			JSONObject o = columnsArray.getJSONObject(i);
			result.put(o.getString("code"), JSON.parseObject(o.getJSONObject("column").toString(), LcDatamodelColumn.class));
		}
		return result;
	}
	
	private static String generateAlterTableSql(String defineCode, String oldDefineTitle, String newDefineTitle, Map<String, LcDatamodelColumn> oldCloumns, Map<String, LcDatamodelColumn> newColumns) {
		StringBuilder sql = new StringBuilder("ALTER TABLE `" + defineCode + "` ");
		List<String> modifySql = new ArrayList<>();
		appendgenModifyComment(modifySql, oldDefineTitle, newDefineTitle);
		appendColumnAddSql(modifySql, oldCloumns, newColumns);
		appendColumnModifySql(modifySql, oldCloumns, newColumns);
		appendColumnDropSql(modifySql, oldCloumns, newColumns);
		return sql + String.join(", ", modifySql);
	}
	
	private static void appendColumnModifySql(List<String> sqls, Map<String, LcDatamodelColumn> oldCloumns, Map<String, LcDatamodelColumn> newColumns) {
		Map<String, LcDatamodelColumn> updateKeys = MapUtils.findUpdatedKeys(oldCloumns, newColumns);
		Collection<LcDatamodelColumn> values = updateKeys.values();
		for(LcDatamodelColumn col : values) {
			sqls.add("MODIFY COLUMN "+ genColumnSql(col));
		}
		
	}
	
	private static void appendColumnAddSql(List<String> sqls, Map<String, LcDatamodelColumn> oldCloumns, Map<String, LcDatamodelColumn> newColumns) {
		Map<String, LcDatamodelColumn> updateKeys = MapUtils.findNewKeys(oldCloumns, newColumns);
		Collection<LcDatamodelColumn> values = updateKeys.values();
		for(LcDatamodelColumn col : values) {
			sqls.add("ADD COLUMN "+ genColumnSql(col));
		}
		
	}
	
	private static void appendColumnDropSql(List<String> sqls, Map<String, LcDatamodelColumn> oldCloumns, Map<String, LcDatamodelColumn> newColumns) {
		Map<String, LcDatamodelColumn> updateKeys = MapUtils.findMissingKeys(oldCloumns, newColumns);
		Collection<LcDatamodelColumn> values = updateKeys.values();
		for(LcDatamodelColumn col : values) {
			sqls.add("DROP COLUMN "+ col.getCode());
		}
	}
	
	private static void appendgenModifyComment(List<String> sqls, String oldDefineName, String newDefineName) {
		if(StringUtils.areSame(oldDefineName, newDefineName)) {
			return;
		} else {
			sqls.add("COMMENT '" + newDefineName + "'");
		}
	}
	
	/**
	 * 生成表描述信息
	 * @param define
	 * @param columns
	 * @return
	 */
	public static String genTableDesc(LcDatamodelDefine define, List<LcDatamodelColumn> columns) {
		JSONObject json = new JSONObject();
		json.put("code", define.getCode());
		json.put("title", define.getTitle());
		JSONArray columnsArray = new JSONArray(); 
		for(LcDatamodelColumn column : columns) {
			JSONObject o = new JSONObject();
			o.put("code", column.getCode());
			o.put("column",  JSON.toJSONString(column));
			columnsArray.add(o);
		}
		json.put("columns", columnsArray);
		return json.toString();
	}

	public static List<LcDatamodelColumn> genDefaultColumns(String defineCode) {
		List<LcDatamodelColumn> result = new ArrayList<LcDatamodelColumn>();
		result.add(LcDatamodelColumn.builder().code("ID").title("ID").defineCode(defineCode).lengthM(20).nullable(false)
				.orderNum(0).pkey(true).type(LcDatamodelColumnTypeEnum.BIGINT.getCode()).attr("1").build());
		result.add(LcDatamodelColumn.builder().code("CODE").title("代码").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(1).pkey(false).type(LcDatamodelColumnTypeEnum.VARCHAR.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("TITLE").title("名称").defineCode(defineCode).lengthM(200)
				.nullable(true).orderNum(2).pkey(false).type(LcDatamodelColumnTypeEnum.VARCHAR.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("CREATE_BY").title("创建人").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(3).pkey(false).type(LcDatamodelColumnTypeEnum.VARCHAR.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("CREATE_TIME").title("创建时间").defineCode(defineCode).lengthM(0)
				.nullable(true).orderNum(4).pkey(false).type(LcDatamodelColumnTypeEnum.DATETIME.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("UPDATE_BY").title("修改人").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(5).pkey(false).type(LcDatamodelColumnTypeEnum.VARCHAR.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("UPDATE_TIME").title("修改时间").defineCode(defineCode).lengthM(0)
				.nullable(true).orderNum(6).pkey(false).type(LcDatamodelColumnTypeEnum.DATETIME.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("REMARK").title("备注").defineCode(defineCode).lengthM(255)
				.nullable(true).orderNum(7).pkey(false).type(LcDatamodelColumnTypeEnum.VARCHAR.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("USER_ID").title("所属用户ID").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(8).pkey(false).type(LcDatamodelColumnTypeEnum.BIGINT.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("DEPT_ID").title("所属部门ID").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(9).pkey(false).type(LcDatamodelColumnTypeEnum.BIGINT.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		//工作流相关
		result.add(LcDatamodelColumn.builder().code("WORKFLOW_STATUS").title("工作流状态").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(10).pkey(false).type(LcDatamodelColumnTypeEnum.CHAR.getCode()).mappingType(LcDatamodelColumnMappingTypeEnum.EM.getCode()).mapping("workflow_status")
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("SUBMIT_USER_ID").title("提交人ID").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(11).pkey(false).type(LcDatamodelColumnTypeEnum.BIGINT.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("SUBMIT_DEPT_ID").title("提交部门ID").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(12).pkey(false).type(LcDatamodelColumnTypeEnum.BIGINT.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("SUBMIT_TIME").title("提交时间").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(13).pkey(false).type(LcDatamodelColumnTypeEnum.DATETIME.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("LAST_AUDIT_TIME").title("最后审核时间").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(14).pkey(false).type(LcDatamodelColumnTypeEnum.DATETIME.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		result.add(LcDatamodelColumn.builder().code("LAST_AUDIT_USER_ID").title("最后审核人ID").defineCode(defineCode).lengthM(64)
				.nullable(true).orderNum(15).pkey(false).type(LcDatamodelColumnTypeEnum.BIGINT.getCode())
				.attr(LcDatamodelColumnAttrEnum.SYSTEM.getCode()).build());
		return result;
	}

	/**
	 * 生產創建表信息
	 * @param columns
	 * @param defineCode
	 * @param defineTitle
	 * @return
	 */
	public static String genCreateSql(LcDatamodelDefine define) {
		List<LcDatamodelColumn> columns = findColumnsFromDesc(define.getWhzDesc());
		String defineCode = define.getCode();
		String defineTitle = define.getWhzTitle();
		StringBuilder sql = new StringBuilder("CREATE TABLE `" + defineCode + "` (");
		List<String> columnsSql = new ArrayList<>();
		for (LcDatamodelColumn column : columns) {
			columnsSql.add(genColumnSql(column));
		}
		sql.append(String.join(", ", columnsSql));
		sql.append(")");
		if (StringUtils.isNotEmpty(defineTitle)) {
			sql.append(" COMMENT='").append(defineTitle).append("'");
		}
		return sql.toString();
	}
	
	private static String genColumnSql (LcDatamodelColumn column) {
		String columnDefinition = "`" + column.getCode() + "` " + apendComment(apendNullAble(getColumnType(column), column), column);
		if ("ID".equals(column.getCode())) {
			columnDefinition += " AUTO_INCREMENT PRIMARY KEY";
		};
		return columnDefinition;
	}

	private static String apendComment(String sql, LcDatamodelColumn column) {
		return sql + " COMMENT '" + column.getTitle() + "'";
	}

	private static String apendNullAble(String typeSql, LcDatamodelColumn column) {
		if (column.getNullable() == null || column.getNullable()) {
			return typeSql + " NULL";
		} else {
			return typeSql + " NOT NULL";
		}
	}

	private static String getColumnType(LcDatamodelColumn column) {
		String type = column.getType();
		if (LcDatamodelColumnTypeEnum.BIGINT.getCode().equals(type)) {
			return "BIGINT(64)";
		} else if (LcDatamodelColumnTypeEnum.BOOLEAN.getCode().equals(type)) {
			return "CHAR(1)";
		} else if (LcDatamodelColumnTypeEnum.CHAR.getCode().equals(type)) {
			return "CHAR(1)";
		} else if (LcDatamodelColumnTypeEnum.DATE.getCode().equals(type)) {
			return "DATE";
		} else if (LcDatamodelColumnTypeEnum.DATETIME.getCode().equals(type)) {
			return "DATETIME";
		} else if (LcDatamodelColumnTypeEnum.INT.getCode().equals(type)) {
			return "INT(32)";
		} else if (LcDatamodelColumnTypeEnum.MULTIPLE.getCode().equals(type)) {
			return "VARCHAR(" + column.getLengthM() + ")";
		} else if (LcDatamodelColumnTypeEnum.DECIMAL.getCode().equals(type)) {
			return "DECIMAL(" + column.getLengthM() + "," + column.getLengthD() + ")";
		} else if (LcDatamodelColumnTypeEnum.VARCHAR.getCode().equals(type)) {
			return "VARCHAR(" + column.getLengthM() + ")";
		}
		return "VARCHAR(255)";
	}

}
